# C:/Python27/TextResuming/sql_manage.py
# a short script for retrieve news from server and save it as indexed news
# Author : @ismailsunni

import html_parser
from db_control import db_conn
from util import write_list, read_text_file
from is_parser import load_index_news
from is_parser import parse_text_to_sentences
from text import create_segmented_news

# database variable
db_host = '192.168.10.83' #Ina
#db_host = '192.168.10.73' #Eng
db_user = 'root'
#db_password = 'eBdeskindonesi@123'
db_password = 'baddumtss'
db_name = 'mm_prod' #ina
#db_name = 'mmprod_my20120427' #eng

news_table = 'news'
	
class sql_manage():
	'''class for reading from server and create news file'''

	def __init__(self):
		
		self.db = db_conn(db_host, db_user, db_password, db_name)
		self.news = []
		self.fail_queries = []

	def read_news(self, link = "", title = ""):
		'''Retrieve news by link and title'''
		
		query = 'SELECT n_title, n_link, n_content FROM ' + news_table
		link = link.replace(" ", "")
		where = ' WHERE n_link LIKE "%' + link[:-1] + '%"'

		retval = self.db.read(query + where)
		if retval == None:
			self.fail_queries.append(query + where)
		elif len(retval) == 0:
			self.fail_queries.append(query + where)
			new_query = 'SELECT n_title, n_link, n_content FROM ' + news_table
			new_where = ' WHERE n_title LIKE "%' + title[:-1] + '%"'
			new_retval = self.db.read(new_query + new_where)
			
			if new_retval == None:
				self.fail_queries.append("None " + new_query + new_where)
			elif len(new_retval) == 0:
				self.fail_queries.append("=0 " + new_query + new_where)
			elif len(new_retval) > 1:\
				# If return more than one result, choose the first one
				self.fail_queries.append(">1 " + new_query + new_where)
				news = (new_retval[0][0], new_retval[0][1], new_retval[0][2])
				self.news.append(news)
			else:
				for row in new_retval:
					news = (row[0], row[1], row[2])
					self.news.append(news)
				
		elif len(retval) > 1:
			self.fail_queries.append(query + where)
		
		else:
			for row in retval:
				news = (row[0], row[1], row[2])
				self.news.append(news)
	
	def save_news(self, pre_format_name, post_format_name, num_news, start_idx = 1):
		'''Save news to text tile.
			
			@parameters
			@pre_format_name : prefix file name, path is included 
			@post_format_name : suffix file name
			@num_news : num of news to be written
			@start_idx : starting index.'''
			
		if num_news >= len(self.news):
			num_news = len(self.news)
		elif num_news < 0:
			num_news = 0
		i = start_idx

		for n in self.news[:num_news]:
			# strip tag
			content = html_parser.strip_tags(n[2], True)
			content_split = content.split('\n')
			list_segment = []
			j = 0
			for item in content_split:
				if item.isspace() == False and len(item) > 0:
					while item[0] == ' ':
						item = item[1:]
					segment = '[' + str(j) + ']' + item
					j += 1
					list_segment.append(segment)
			text = [n[0], '\n', n[1], '\n']
			text.extend(list_segment) 
			# save
			write_list(pre_format_name + str(i) + post_format_name,  text, True)
			i += 1
			
		# save failed queries
		write_list("fail_queries.txt",  self.fail_queries, True)
	
	
# main function
def main():
	print 'start'
	sm = sql_manage()
	link = []
	link = read_text_file('link_ina.txt')
	title = read_text_file('title_ina.txt')
	if len(link) != len(title):
		return
	
	#print link
	for i in [i for i in xrange(len(link))]:
		sm.read_news(link[i], title[i])
	print len(sm.news)
	sm.save_news('data/', '_ID.txt', 120)
	
if __name__ == '__main__':
	main()


		
		